Oracle has neat standard functionality to transform a query to qml.
Sample code:
select dbms_xmlgen.getxml(’select * from user_tables’)
from dual;
Create a virtual table with numbers
Sometimes I need a virtual table with increasing number.
So a table with contents:
1
2
3
4
5
For this purpose a number of implementations are used on projects I worked on.
For example:
* A real table with increasing number.
* select rownum on all_objects where rownum < 1000;
* A pipelined function that returns numbers from a for loop
* select rownum
from (SELECT 1 FROM dual GROUP BY CUBE(1,2,3,4,5,6) )
where rownum < 50;
But today a post on the Amis weblog (http://technology.amis.nl/blog/?p=1751) gave me a more elegant solution using connect by level.
SELECT level
FROM dual
CONNECT BY LEVEL <= 365;
I will use that the next time.
XPath slow
I’m working on a SOA project that handles a lot of XML messages.
Every message that goes through the system will be logged.
To make it uniform all messages are transformed to SOAP messages,
and when receiving SOAP messages we need to get the important fields
from a message in order to log these.
I used the JAMon tool to monitor the performance of handling these messages, and it turned out that the parsing of XML with XPath and Java 5 took (relatively) much time. Parsing a message took 19ms on average. And we already cached the XPathExpression.
It’s a very simple XML message with 1 root element and 10 child elements.
I needed to extract the value of 8 of these elements.
I tried to rebuild the XPath expression with different methods for extracting fields from an XML document with the following results:
Traversing with DOM: 21 times as fast
Using JDOM: 17 times as fast
Using indexOf string manipulation: 881 times as fast!
So using indexOf to extract fields in XML documents can be much faster!
Calculating aggregates on a timestamp
Today I needed to use aggregates on the Oracle TIMESTAMP datatype.
We had a table DTM_MESSAGES with the columns received and responded,
both with datatype timestamp.
This table is used to measure the responsetime of a webservice.
When we substract these 2 columns with the query:
select received- responded from dtm_messages
We get a time-interval as response, not a number.
Because of this we can not use aggregate functions like sum or avg on the
difference between 2 timestamps.
select avg(received- responded) from dtm_messages
gives:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL TO SECOND.
If we want to use an aggregate function, we need to convert this all to seconds.
Then we can use an aggregate function again.
So:
select avg(
(extract(day from responded)-extract(day from received))*86400
+ (extract(hour from responded)-extract(hour from received))*3600
+ (extract(minute from responded)-extract(minute from received))*60 + (extract(second from responded) -extract(second from received))) avg_seconds from dtm_messages
works fine
Java StringTokenizer
Today I discovered that StringTokenizer is considered a legacy class by Sun. It’s retained for compatibility reasons although its use is discouraged in new code. Sun recommends the use of the split method in the String class or using the java.util.regex package instead.
By using the split method you lose the option to include the seperator-character in the split set. For parsing EDI I wanted to split an EDI segment on seperator characters + or : but I want to make sure that the seperator characters are included in the split set, so I can check if the right seperator-character was used in the original file. With StringTokenizer this can easily be done by calling java.util.StringTokenizer tokenizer=new java.util.StringTokenizer (segment,”+:”,true);
Job scheduling from Oracle 10g with dbms_scheduler
In the old days we used DBMS_JOB to schedule jobs in the database.
For more complex scheduling a program like Redwood Scheduler was used.
In Oracle 10g Oracle introduced DBMS_SCHEDULER.
The DBMS_JOB package is now depricated and in Oracle 10g it’s only
provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not
be used any more, because is could not exist in a future version of Oracle.
With DBMS_SCHEDULER Oracle procedures and functions can be executed.
Also binary and shell-scripts can be scheduled.
The new scheduler has 3 main components:
– A schedule
– A program
– A job
Rights
If you have DBA rights you can do all the scheduling.
For administering job scheduling you need the priviliges belonging to
the SCHEDULER_ADMIN role.
To create and run jobs in your own schedule you need the ‘CREATE JOB’
privilege.
With DBMS_JOB you needed to set an initialization parameter to start a
job coordinator
background process. With Oracle 10g DBMS_SCHEDULER this is not needed
any more.
If you want to user resource plans and/or consumer groups you need to
set a system parameter:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
Getting started quickly
To quickly get a job running, you can use code like this:
begin dbms_scheduler.create_job( job_name => 'DEMO_JOB_SCHEDULE' ,job_type => 'PLSQL_BLOCK' ,job_action => 'begin package.procedure(''param_value''); end; ' ,start_date => '01/01/2006 02:00 AM' ,repeat_interval => 'FREQ=DAILY' ,enabled => TRUE ,comments => 'Demo for job schedule.'); end; /
This schedules a pl/sql block to be executed daily starting 1/1/2006
02:00 AM.
You can schedule things like this, but DBMS_SCHEDULER can reuse
components.
You can build a schedule using components like program, schedule, job,
job class
and window. We will now discuss these components in detail.
Program
The program component is a collection of metadata about what will be run by the scheduler.
This includes information such as the program name, the type of program, and information about
arguments passed to the program.
Code example
begin dbms_scheduler.create_program ( program_name => 'DEMO_JOB_SCHEDULE' ,program_type => 'STORED_PROCEDURE' ,program_action => 'package.procedure' ,number_of_arguments => 1 ,enabled => FALSE ,comments => 'Demo for job schedule.'); dbms_scheduler.define_program_argument ( program_name => 'DEMO_JOB_SCHEDULE' ,argument_position => 1 ,argument_name => 'kol1' ,argument_type => 'VARCHAR2' ,default_value => 'default' ); dbms_scheduler.enable(name => 'DEMO_JOB_SCHEDULE'); end; /
The parameter program_type can have one of the following values:
‘PLSQL_BLOCK’, ‘STORED_PROCEDURE’,’EXECUTABLE’.
dbms_scheduler also allows to execute shell scripts (Windows: *.bat
files) and
executables.
Schedule
A schedule defines the frequence and date/time when a job is executed.
Similar to programs, schedules are database entities and can be saved in the database. The same schedule can be used by multiple jobs.
example code
begin dbms_scheduler.create_schedule( schedule_name => 'DEMO_SCHEDULE' , start_date => '01/01/2006 22:00:00' , repeat_interval => 'FREQ=WEEKLY' , comments => 'Weekly at 22:00'); END; / To drop the schedule: begin dbms_scheduler.drop_schedule( schedule_name => 'DEMO_SCHEDULE' , force => TRUE ); end; /
Calendar expresions can have one of these values:
‘Yearly’,’Monthly’,’Weekly’,’Daily’,’Hourly’,’Minutely’,’Secondely’
Job
A job specifies what needs to executed and when. This can be a pl/sql procedure, c program, shell script
or other program.
You can specify a program and a schedule as part of a job definition, or you can use an existing program or schedule.
A job can belong to only 1 job class.
Code example
begin dbms_scheduler.create_job( job_name => 'DEMO_JOB1' , program_name =>'DEMO_JOB_SCHEDULE' , schedule_name =>'DEMO_SCHEDULE' , enabled => FALSE , comments => 'Run demo program every week at 22:00'); dbms_scheduler.set_job_argument_value( job_name => 'DEMO_JOB1' , argument_position => 1 , argument_value => 'param1'); dbms_scheduler.enable('DEMO_JOB1'); commit; end; / Or start shell script begin dbms_scheduler.create_job ( job_name => 'RUN_SHELL1', schedule_name => 'DEMO_SCHEDULE', job_type => 'EXECUTABLE', job_action => '/home/test/run_script.sh', enabled => true, comments => 'Run shell-script' ); end; /
Monitoring job-scheduling
Jobs can be monitored using Oracle Enterprise Manager 10g.
It’s also possible to use a number of views that have been created in
Oracle 10g.
We will discuss some of these views here.
To show details on job run: select log_date , job_name , status , req_start_date , actual_start_date , run_duration from dba_scheduler_job_run_details
To show running jobs: select job_name , session_id , running_instance , elapsed_time , cpu_used from dba_scheduler_running_jobs;
To show job history: select log_date , job_name , status from dba_scheduler_job_log; show all schedules: select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules; show all jobs and their attributes: select * from dba_scheduler_jobs show all program-objects and their attributes select * from dba_scheduler_programs; show all program-arguments: select * from dba_scheduler_program_args;
Return a file with a Servlet
Here is some sample java code.
This code returns a file from a Servlet:
package com.javaoracleblog.servlets;import java.io.*; import javax.servlet.*; import javax.servlet.http.*;/** * Servlet to download text to a file. * * @author Edwin Korsten * @version 1.0 */ public class Download extends HttpServlet {/** * Return the contents of the requested file. */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { PrintWriter out = null; try {String fileName = "test.txt"; response.setContentType("application/x-msdownload"); response.addHeader("Content-disposition", "attachment; filename=\"" + fileName + "\""); out = response.getWriter(); out.write("This is the contents of the file"); } catch (Exception e) { if (out == null) { response.setContentType("text/html"); out = response.getWriter(); } out.write("” + “Error trying to get file : “); out.write(e.toString()); out.write(”"); } out.close(); }public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { doGet(request, response); }}
Creating CBO statistics in Oracle
Since I am using the Cost Based Optimizer in Oracle (Oracle 7.3), I created the statistics needed for the optimizer with the command ‘ANALYZE TABLE’.I recently found out that since Oracle 7.4 this is not the optimal statement for creating statistics.
In Oracle 7.3.4 and Oracle 8.0 you should use the DBMS_UTILITY.ANALYZE_SCHEMA command.
From 8i you should use the DBMS_STATS.GATHER_SCHEMA_STATS command.
Analyze table
The analyze table can be used to create statistics for 1 table, index or cluster.
Syntax:
ANALYZE table tableName {compute|estimate|delete) statistics options
ANALYZE table indexName {compute|estimate|delete) statistics options
ANALYZE cluster clusterName {compute|estimate|delete) statistics options
Code examples ANALYZE table scott compute statistics; ANALYZE table scott estimate statistics sample 25 percent; ANALYZE table scott estimate statistics sample 1000 rows; analyze index sc_idx compute statistics; analyze index sc_idx validate structure;
DBMS_UTILITY.ANALYZE_SCHEMA
With DBMS_UTILITY.ANALYZE_SCHEMA you can gather all the statistics for
all the tables,
clusters and indexes of a schema.
Code examples exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE'); exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000); exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25); exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');
Note: It’s also possible to analyze the whole database with the
DBMS_UTILITY.ANALYZE_DATABASE(’COMPUTE’); command.
DBMS_STATS.GATHER_SCHEMA_STATS
From Oracle 8i the DBMS_STATS package is the preferred method
Oracle list a number of benefits to using it including parallel
execution, long term storage of statistics
and transfer of statistics between servers. Once again, it follows a
similar format to the other methods:
Syntax:
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample
, method_opt,degree,granularity,cascade,stattab, statid,options,statown
,no_invalidate, gather_temp,gather_fixed);
Code examples: exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE); exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE); EXEC DBMS_STATS.gather_schema_stats(ownname => 'SCOTT', estimate_percent => 25); EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES'); EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK'); exec DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');
Note: It’s also possible to gather statistics for the whole database
with the
DBMS_STATS.gather_database_stats; command.
Transfering statistics between database.
It can be very handy to use production statistics on your development
database,
so that you can forecast the optimizer behavior.
You can do this the following way:
1. Create the statistics table.
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>’SCHEMA_NAME’
,stat_tab => ‘STATS_TABLE’
, tblspace => ‘STATS_TABLESPACE’);
Example:
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>’SYSTEM’,stat_tab =>
‘STATS_TABLE’);
2. Export statistics to statistics table
EXEC
DBMS_STATS.EXPORT_SCHEMA_STATS(’ORIGINAL_SCHEMA’
,’STATS_TABLE’,NULL,’SYSTEM’);
3. Import statistics into the data dictionary.
exec
DBMS_STATS.IMPORT_SCHEMA_STATS(’NEW_SCHEMA’,’STATS_TABLE’,NULL,’SYSTEM’);
4. Drop the statistics table.
exec DBMS_STATS.DROP_STAT_TABLE(’SYSTEM’,’STATS_TABLE’);
Create excel from JDBC resultset
Here is some simple code to transform a JDBC resulset to an excel-sheet.
You can also use a tool like Apache POI, but this one is easy and simple.
package com.javaoracleblog.servlets;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager;
import java.sql.ResultSet; import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.math.BigDecimal;
public class ExcelWriter {
protected String excelXmlHeader() { return ("/n”>http://www.w3.org/TR/REC-html40”>n” + “ n” + ” n” + “n” + ” n” + ”
“);
for (int j = 0; j < columnNames.length; j++) { result.append("
| ” + columnNames[j] + “ |
“);
}
public static void main(String[] args) { try { String driverName = "oracle.jdbc.driver.OracleDriver"; Class.forName(driverName);
Connection conn; String url = "jdbc:oracle:thin:@orakt02:1521:aridev01"; conn = DriverManager.getConnection(url, "ari", "ari"); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM ari_config");
PrintWriter out; try { out = new PrintWriter(new OutputStreamWriter( new FileOutputStream("c:/test.xls"))); ExcelWriter ew = new ExcelWriter(); ew.writeResultset(out, rs); out.close();
} catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); }
} catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); }
}
}